﻿CREATE PROCEDURE [dbo].[utl_RemoveHousekeeping]
@Table [sysname]=NULL
AS
BEGIN
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
	
	DECLARE @SQL                      NVARCHAR(MAX)
	DECLARE @ErrorCode                INT
	DECLARE @RowsAffected             INT
	DECLARE @Message                  VARCHAR(MAX)
	DECLARE @ColumnDefaultConstraint  sysname
	
	-- see if table name was passed
	IF @Table IS NULL
	BEGIN
	    SET @Message = 'Must pass table name.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 1
	END
	
	-- see if table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 2
	END
	
	-- disable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not disable triggers for table ''' + @Table + '''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	-- remove trigger
	IF dbo.fn_TriggerExists(@Table, @Table + '_HouseKeeping_InsUpdTrg') = @True
	BEGIN
	    SET @SQL = 'DROP TRIGGER ' + @Table + '_HouseKeeping_InsUpdTrg'
	    EXECUTE sp_executesql @SQL
	END
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not drop trigger ''' + @Table + '_HouseKeeping_InsUpdTrg.'''
	    RAISERROR(@Message, 16, 1)
	    RETURN 4
	END
	
	-- drop CreateDt column
	IF dbo.fn_ColumnExists(@Table, 'CreateDt') = @True
	BEGIN
	    -- drop default constraint first
	    SET @ColumnDefaultConstraint = dbo.fn_ColumnDefaultConstraint(@Table, 'CreateDt')
	    IF @ColumnDefaultConstraint IS NOT NULL
	    BEGIN
	        SET @SQL = 'ALTER TABLE ' + @Table + ' DROP CONSTRAINT ' + @ColumnDefaultConstraint
	        EXECUTE sp_executesql @SQL
	        
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Could not drop default constraint ''' + @ColumnDefaultConstraint + '''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 5
	        END
	    END
	    
	    -- drop column
	    SET @SQL = 'ALTER TABLE ' + @Table + ' DROP COLUMN CreateDt'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not drop column ''' + @Table + '.dbo.CreateDt.'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 6
	    END
	END
	
	-- drop CreateUid column    
	IF dbo.fn_ColumnExists(@Table, 'CreateUid') = @True
	BEGIN
	    -- drop default constraint first
	    SET @ColumnDefaultConstraint = dbo.fn_ColumnDefaultConstraint(@Table, 'CreateUid')
	    IF @ColumnDefaultConstraint IS NOT NULL
	    BEGIN
	        SET @SQL = 'ALTER TABLE ' + @Table + ' DROP CONSTRAINT ' + @ColumnDefaultConstraint
	        EXECUTE sp_executesql @SQL
	        
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Could not drop default constraint ''' + @ColumnDefaultConstraint + '''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 7
	        END
	    END
	    
	    -- drop column
	    SET @SQL = 'ALTER TABLE ' + @Table + ' DROP COLUMN CreateUid'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not drop column ''' + @Table + '.dbo.CreateUid.'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 8
	    END
	END
	
	-- drop RevMdlName column
	IF dbo.fn_ColumnExists(@Table, 'RevMdlName') = @True
	BEGIN
	    -- drop default constraint first
	    SET @ColumnDefaultConstraint = dbo.fn_ColumnDefaultConstraint(@Table, 'RevMdlName')
	    IF @ColumnDefaultConstraint IS NOT NULL
	    BEGIN
	        SET @SQL = 'ALTER TABLE ' + @Table + ' DROP CONSTRAINT ' + @ColumnDefaultConstraint
	        EXECUTE sp_executesql @SQL
	        
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Could not drop default constraint ''' + @ColumnDefaultConstraint + '''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 9
	        END
	    END
	    
	    -- drop column	
	    SET @SQL = 'ALTER TABLE ' + @Table + ' DROP COLUMN RevMdlName'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not drop column ''' + @Table + '.dbo.RevMdlName.'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 10
	    END
	END
	
	-- drop RevDt column
	IF dbo.fn_ColumnExists(@Table, 'Revdt') = @True
	BEGIN
	    -- drop default constraint first
	    SET @ColumnDefaultConstraint = dbo.fn_ColumnDefaultConstraint(@Table, 'Revdt')
	    IF @ColumnDefaultConstraint IS NOT NULL
	    BEGIN
	        SET @SQL = 'ALTER TABLE ' + @Table + ' DROP CONSTRAINT ' + @ColumnDefaultConstraint
	        EXECUTE sp_executesql @SQL
	        
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Could not drop default constraint ''' + @ColumnDefaultConstraint + '''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 11
	        END
	    END
	    
	    -- drop column	
	    SET @SQL = 'ALTER TABLE ' + @Table + ' DROP COLUMN RevDt'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not drop column ''' + @Table + '.dbo.RevDt.'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 12
	    END
	END
	
	-- drop RevUid column
	IF dbo.fn_ColumnExists(@Table, 'RevUid') = @True
	BEGIN
	    -- drop default constraint first
	    SET @ColumnDefaultConstraint = dbo.fn_ColumnDefaultConstraint(@Table, 'RevUid')
	    IF @ColumnDefaultConstraint IS NOT NULL
	    BEGIN
	        SET @SQL = 'ALTER TABLE ' + @Table + ' DROP CONSTRAINT ' + @ColumnDefaultConstraint
	        EXECUTE sp_executesql @SQL
	        
	        SELECT @ErrorCode = @@ERROR,
	               @RowsAffected = @@ROWCOUNT
	        
	        IF @ErrorCode <> 0
	        BEGIN
	            SET @Message = 'Could not drop default constraint ''' + @ColumnDefaultConstraint + '''.'
	            RAISERROR(@Message, 16, 1)
	            RETURN 13
	        END
	    END
	    -- drop column	
	    SET @SQL = 'ALTER TABLE ' + @Table + ' DROP COLUMN RevUid'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not drop column ''' + @Table + '.dbo.RevUid.'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 14
	    END
	END
	
	-- enable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not enable triggers for table ''' + @Table + '''.' 
	    RAISERROR(@Message, 16, 1)
	    RETURN 15
	END
	
	RETURN 0
END

